﻿using System;
using System.Data.SqlClient;
using System.Data;
using System.Windows.Forms;




namespace _201206QuanLyBanHang
{
   public class DataConn
    {
       public SqlConnection cnn;
        SqlDataAdapter da;
        SqlCommand cmd;
        private DataTable dt;      


        //string strConnection = @"server=.;database=QuanLyBanHang;uid=sa;pwd=123";
        string strConnection = @"server=.;database=QuanLyBanHang;Trusted_Connection=True";
       
        public DataConn()
        {
            try
            {
                cnn = new SqlConnection(strConnection);
                cmd = cnn.CreateCommand();
            }
            catch (SqlException sqle)
            {

                System.Windows.Forms.MessageBox.Show(sqle.Message);
            }
            
        }
       





        public DataSet ExectueQueryDataset(string strSql, CommandType ct, params SqlParameter[] p)
        {
                     
                cnn.Open();
                cmd.CommandText = strSql;
                cmd.CommandType = ct;
                
                da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                cnn.Close();
            return ds;
        }
       //TTD tao
        public DataSet ExectueQueryDatasetreport(string strSql,string table, CommandType ct, params SqlParameter[] p)
        {

            cnn.Open();
            cmd.CommandText = strSql;
            cmd.CommandType = ct;
            da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            try
            {
                da.Fill(ds,table);
                
            }
            catch (SqlException sqle)
            {

                System.Windows.Forms.MessageBox.Show(sqle.Message);
            }
            cnn.Close();
            return ds;
            
        }
        //end TTD tao 
        public DataTable ExectueQueryBaoCao(string strSql, CommandType ct, ref string error, params SqlParameter[] param)
        {
            DataTable dt = new DataTable();
            cnn.Open();
            cmd.Parameters.Clear();
            cmd.CommandText = strSql;
            cmd.CommandType = ct;
            foreach (SqlParameter p in param)
                cmd.Parameters.Add(p);
            try
            {
                da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                dt = ds.Tables[0];
            }
            catch (SqlException ex)
            {
                error = ex.Message;
            }
            finally
            {
                cnn.Close();
            }
            return dt; 
        }

       //test
        public DataTable ExectueQueryBaoCao1(string strSql, CommandType ct)
        {
            DataTable dt = new DataTable();
            cnn.Open();
            cmd.Parameters.Clear();
            cmd.CommandText = strSql;
            cmd.CommandType = ct;
           
            try
            {
                da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                dt = ds.Tables[0];
            }
            catch (SqlException ex)
            {
                //error = ex.Message;
                MessageBox.Show(ex.Message);
            }
            finally
            {
                cnn.Close();
            }
            return dt;
        }
        public DataTable ExectueQueryBaoCao1(string strSql, CommandType ct, params SqlParameter[] param)
        {
            DataTable dt = new DataTable();
            cnn.Open();
            cmd.Parameters.Clear();
            cmd.CommandText = strSql;
            cmd.CommandType = ct;
            foreach (SqlParameter p in param)
              cmd.Parameters.Add(p);
            try
            {
            da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);
            dt = ds.Tables[0];
            }
            catch (SqlException ex)
            {
                //error = ex.Message;
                MessageBox.Show(ex.Message);
            }
            finally
            {
            cnn.Close();
            }
            return dt;
        }
        public bool MyExecuteNonQuery(string strSql, CommandType ct, ref string error, params SqlParameter[] param)
        {
            bool f = false;
            cnn.Open();
            cmd.Parameters.Clear();
            cmd.CommandText = strSql;
            cmd.CommandType = ct;
            foreach (SqlParameter p in param)
                cmd.Parameters.Add(p);
            try
            {
                cmd.ExecuteNonQuery();
                f = true;
            }
            catch (SqlException ex)
            {
                error = ex.Message;
            }
            finally
            {
                cnn.Close();
            }
            return f;
        }

        public bool MyExecuteNonQuery1(string strSql, CommandType ct, ref string error, params SqlParameter[] param)
        {
            bool f = false;
            //cnn.Open();
            cmd.Parameters.Clear();
            cmd.CommandText = strSql;
            cmd.CommandType = ct;
            foreach (SqlParameter p in param)
                cmd.Parameters.Add(p);
            try
            {
                cmd.ExecuteNonQuery();
                f = true;
            }
            catch (SqlException ex)
            {
                error = ex.Message;
            }
            finally
            {
                cnn.Close();
            }
            return f;
        }






        public object lay(string strsql, CommandType ct, ref string error)
        {
            cnn.Open();
            cmd.CommandText = strsql;
            cmd.CommandType = ct;
            object obj;
            try
            {
                obj = cmd.ExecuteScalar();
            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                cnn.Close();
            }
            
            return obj;
        }
        public int ChkMa(string strSql, CommandType ct, ref string error, params SqlParameter[] param)
        {
            int Ton_Tai = 0;
            cnn.Open();
            cmd.Parameters.Clear();
            cmd.CommandText = strSql;
            cmd.CommandType = ct;
            //foreach (SqlParameter p in param)
             //   cmd.Parameters.Add(p);
            try
            {
                object obj = cmd.ExecuteScalar();
                if (obj != null)
                    Ton_Tai = (int)obj;
            }
            catch (SqlException ex)
            {
                error = ex.Message;
            }
            finally
            {
                cnn.Close();
            }
            return Ton_Tai;
        }
        public int getThongTin(string strSql, CommandType ct, ref string error)
        {
            int Thong_Tin = 0;
            cnn.Open();
            cmd.Parameters.Clear();
            cmd.CommandText = strSql;
            cmd.CommandType = ct;
            try
            {
                object obj = cmd.ExecuteScalar();
                if (obj != null)
                    Thong_Tin = (int)obj;
            }
            catch (SqlException ex)
            {
                error = ex.Message;
            }
            finally
            {
                cnn.Close();
            }
            return Thong_Tin;
        }

        public double getCongNo(string strSql, CommandType ct)
        {
            double Thong_Tin = 0;
            cnn.Open();
            cmd.Parameters.Clear();
            cmd.CommandText = strSql;
            cmd.CommandType = ct;
            try
            {
                object obj = cmd.ExecuteScalar();
                if (obj != null)
                    Thong_Tin = (double)obj;
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message);

            }
            finally
            {
                cnn.Close();
            }
            return Thong_Tin;
        }

       public int ChkSo_Hd(string strSql, CommandType ct, ref string error)
        {
            int Ton_Tai = 0;
            cnn.Open();
            cmd.Parameters.Clear();
            cmd.CommandText = strSql;
            cmd.CommandType = ct;
            try
            {
                object obj = cmd.ExecuteScalar();
                if (obj != null)
                    Ton_Tai = (int)obj;
            }
            catch (SqlException ex)
            {
                error = ex.Message;
            }
            finally
            {
                cnn.Close();
            }
            return Ton_Tai;
        }
        public float getGia_Vt(string strSql, CommandType ct, ref string error)
        {
            float Gia = 0;
            cnn.Open();
            cmd.Parameters.Clear();
            cmd.CommandText = strSql;
            cmd.CommandType = ct;
            try
            {
                object obj = cmd.ExecuteScalar();
                if (obj != null)
                    float.TryParse(obj.ToString(), out Gia);
            }
            catch (SqlException ex)
            {
                error = ex.Message;
            }
            finally
            {
                cnn.Close();
            }
            return Gia;
        }
        //public double TraveDongDuLieu(String sql)
        //{
        //    double GiaSanPhamSQL = 0;

        //    cnn.Open();
        //    SqlCommand mysqlcommand = cnn.CreateCommand();

        //    mysqlcommand.CommandText = sql;
        //    SqlDataReader datareader = mysqlcommand.ExecuteReader();
        //    while (datareader.Read())
        //    {
        //        GiaSanPhamSQL = double.Parse(datareader[0].ToString());
        //    }
        //    datareader.Close();
        //    cnn.Close();//disconnect();
        //    if (GiaSanPhamSQL == 0) //Trường hợp ko có dữ liệu trả về thì mặc định bằng 0
        //        return 0;
        //    return GiaSanPhamSQL;
        //}
        ///LAY DU LIEU VAO COMBOBOX
        ///
        public void fillCombo(string sql, ComboBox cmb, string text, string value)
        {
            cnn.Open();
            cmd = new SqlCommand(sql, cnn);
            da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);

            cmb.DataSource = ds.Tables[0];
            cmb.DisplayMember = text;
            cmb.ValueMember = value;
        }
        
       //public DataTable GetDataTable(String sql)
       // {
       //     //tao dataAdapte, để thực thi câu lệnh truy vấn
       //    // cnn.Open();
       //     da = new SqlDataAdapter(sql, cnn);
       //     //do du lieu vào datatable
       //     DataTable dt = new DataTable();
       //     da.Fill(dt);
       //     return dt;
       // }

       //internal static SqlDataReader ThucHienReader(string select)
       //{

       //}

      public SqlDataReader ThucHienReader(string sql)
       {
           cnn.Open();
           SqlCommand mysqlcommand = cnn.CreateCommand();
           //SqlDataReader datareader;// = new SqlDataReader();
           
           //try
           //{
              mysqlcommand.CommandText = sql;
              SqlDataReader datareader = mysqlcommand.ExecuteReader();
              return datareader;
           //cnn.Close();
           //}
           //catch (SqlException ex)
           //{
               //error = ex.Message;
             //  MessageBox.Show(ex.Message);
           //}
          // finally
           //{
               //cnn.Close();
           //}
           
       }
     
       //Code Minh
      //Hàm lấy dữ liệu và đưa vào datatable
      public DataTable GetDataTable(String sql)
      {
          //tao dataAdapte, để thực thi câu lệnh truy vấn
          cnn.Open();
          da = new SqlDataAdapter(sql, cnn);
          //do du lieu vào datatable
          dt = new DataTable();
          try
          {
              da.Fill(dt);
          }
          catch (Exception ex)
          {
              MessageBox.Show("Lỗi: " + ex.Message);
          }
          cnn.Close();
          return dt;
      }

      //Hàm thực hiện câu truy vấn Insert, Update, Delete
      public Boolean ExecuteQuery(String sql)
      {
          cnn.Open();
          int sodong = 0; // số dòng ảnh hưởng của câu lệnh sql
          try
          {
              SqlCommand cmd = new SqlCommand(sql, cnn);
              sodong = cmd.ExecuteNonQuery();
          }
          catch (Exception ex)
          {
              MessageBox.Show("Lỗi: " + ex.Message);
          }
          cnn.Close();
          if (sodong > 0)
              return true;
          return false;
      }
      public Boolean ExecuteQuery(String sql,params SqlParameter[] param)
      {
          cnn.Open();
          int sodong = 0; // số dòng ảnh hưởng của câu lệnh sql
          try
          {
              SqlCommand cmd = new SqlCommand(sql, cnn);
              cmd.CommandType = CommandType.StoredProcedure;
              foreach (SqlParameter item in param)
              {
                  cmd.Parameters.Add(item);
              }
              sodong = cmd.ExecuteNonQuery();
          }
          catch (Exception ex)
          {
              MessageBox.Show("Lỗi: " + ex.Message);
          }
          cnn.Close();
          if (sodong > 0)
              return true;
          return false;
      }
      //Thực thi Update
      public Boolean ExecuteQueryUpdate(String sql)
      {
          cnn.Open();
          int sodong = 0; // số dòng ảnh hưởng của câu lệnh sql
          try
          {
              SqlCommand mycmd = cnn.CreateCommand();
              mycmd.CommandText = sql;
              sodong = mycmd.ExecuteNonQuery();
          }
          catch (Exception ex)
          {
              MessageBox.Show("Lỗi: " + ex.Message);
          }
          cnn.Close();
          if (sodong > 0)
              return true;
          return false;
      }

      //Viết hàm trả về các dòng dữ liệu trong Table SQL
      public double TraveDongDuLieu(String sql)
      {
          double GiaSanPhamSQL = 0;
            

          cnn.Open();
          SqlCommand mysqlcommand = cnn.CreateCommand();

          mysqlcommand.CommandText = sql;
          SqlDataReader datareader = mysqlcommand.ExecuteReader();
          
          while (datareader.Read())
          {
              
             
                  GiaSanPhamSQL = double.Parse(datareader[0].ToString());
             
           }
          datareader.Close();
          cnn.Close();
          
          
          
          if (GiaSanPhamSQL == 0) //Trường hợp ko có dữ liệu trả về thì mặc định bằng 0
              return 0;
          return GiaSanPhamSQL;
      }
      public String TraveDongDuLieuString(String sql)
      {
          String GiaSanPhamSQL = "";

          cnn.Open();
          SqlCommand mysqlcommand = cnn.CreateCommand();

          mysqlcommand.CommandText = sql;
          SqlDataReader datareader = mysqlcommand.ExecuteReader();
          while (datareader.Read())
          {
              GiaSanPhamSQL = datareader[0].ToString();
          }
          datareader.Close();
          cnn.Close();
          if (GiaSanPhamSQL == "0") //Trường hợp ko có dữ liệu trả về thì mặc định bằng 0
              return "0";
          return GiaSanPhamSQL;
      }
    }
}
